The SQL language

Since Seagate Crystal Reports uses the SQL language to access client/server databases through ODBC, you can better understand the report generating process by understanding some of the SQL clauses (commands) used:

SELECT

The SELECT clause indicates specific data items to retrieve from the database tables. The item retrieved may be the values in a database field (column), or it may be the result of a calculation performed while gathering the data. For example:

SELECT
    TABLEA.'CUSTNAME',
    TABLEA.'STATE'

DISTINCT

The DISTINCT clause forces the query to retrieve only unique (distinct) sets of data. When using the DISTINCT clause, a row of results will be retrieved only once. The previous SELECT statement can be modified to use the DISTINCT clause:

SELECT DISTINCT
    TABLEA.'CUSTNAME',
    TABLEA.'STATE'

FROM

The FROM clause indicates the sources of the database fields specified in the SELECT clause. FROM lists actual database tables that include the fields and records containing the requested data. The FROM clause generated by Seagate Crystal Reports precedes the name of each table with the alias it uses to identify the table in your report. The following example illustrates the FROM clause used with the SELECT clause:

SELECT
    TABLEA.'CUSTNAME',
    TABLEA.'STATE'
FROM
    'TABLEA' TABLEA

WHERE

The WHERE clause has two purposes:

• to specify record selection criteria
• to show how two database tables are joined.

When WHERE is used to specify record selection criteria, it includes a search condition in order to determine which records (rows of data) are to be retrieved. For example:

SELECT
    MYTABLE.'SALESPERSON',
    MYTABLE.'SALESTOTAL'
FROM
    'MYTABLE' MYTABLE
WHERE
    MYTABLE.'SALESTOTAL' < 10000.00

When WHERE is used to specify how two tables are linked, an SQL join operator sits between the two table names. See SQL join types (ODBC data sources).

The following is an example of the WHERE clause joining two tables:

SELECT
    CUSTOMER.'CUST_ID',
    CUSTOMER.'CUST_NAME',
    ORDERS.'AMOUNT'
FROM
    'CUSTOMER' CUSTOMER,
    'ORDERS' ORDERS
WHERE
    CUSTOMER.'CUST_ID' = ORDERS.'CUST_ID'

ORDER BY

The ORDER BY clause specifies that the database records retrieved be sorted according to the values in a specific field. If you do not use the ORDER BY clause, the program retrieves records in the order in which they appear in the original database. If you specify more than one field after the ORDER BY clause, the program sorts the records according to the values in the first field specified, then, within that sort, the program sorts the records by the values in the second field specified, and so on. The following SQL statement uses the ORDER BY clause:

SELECT
    MYTABLE.'COMPANY',
    MYTABLE.'CITY',
    MYTABLE.'STATE'
FROM
    'MYTABLE' MYTABLE
ORDER BY
    MYTABLE.'STATE' ASC,
    MYTABLE.'CITY' ASC

Note:    ASC indicates that the values in the field are sorted in ascending order rather than descending order (DESC). Ascending order sorts letters from A to Z and numbers from 0 to 9.

GROUP BY

The GROUP BY clause retrieves a set of summary data. Instead of retrieving the data itself, GROUP BY groups the data and summarizes each group with an SQL aggregate function. The server returns only the summarization information for each group to Seagate Crystal Reports.

For example:

SELECT
    MYTABLE.'STATE',
    MYTABLE.'ZIPCODE',
    SUM (MYTABLE.'SALES')
FROM
    'MYTABLE' MYTABLE
GROUP BY
    MYTABLE.'STATE',
    MYTABLE.'ZIPCODE'